package com.vogue.supplymall.cross.domain.prodiver;

import com.vogue.ssm.database.provider.BaseSqlProvider;
import com.vogue.supplymall.common.domain.dto.admin.OrganManagementInfo;
import org.apache.commons.lang3.StringUtils;

/**
 * The type Opened organ sql provider.
 * Create in 2018-06-11
 * @author zhangliang
 */
public class OpenedOrganSqlProvider extends BaseSqlProvider {

    /**
     * Find string.
     *
     * @param info the info
     * @return the string
     */
    public String find(OrganManagementInfo info) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT  ")
                .append(" ao.id,ao.tpsystem,acp.id acpid,bc.besellerdate,ao.syscode,ao.opendate,mo.orgname organname,ao.tel, ")
                .append(" ao.syscodeid,group_concat(DISTINCT ap.platform) platform,ap1.platform bind ")
                // 店铺总数
                .append(" ,(select COUNT(DISTINCT matchid) count FROM shop where syscodeid = ao.syscodeid) totalShopCnt ")
                // 待审核店铺数量
                .append(" ,(select COUNT(id) count from licence_apply where syscodeid = ao.syscodeid and applystatue = 0) waitCheckShopCnt ")
                // 已开通店铺数量
                .append(" ,(select COUNT(id) count from licence_apply where syscodeid = ao.syscodeid and applystatue = 1) openedShopCnt ")
                // 未通过审核店铺数量
                .append(" ,(select COUNT(id) count from licence_apply where syscodeid = ao.syscodeid and applystatue = 2) deniedShopCnt ")
                // 续约&变更店铺数量
                .append(" ,(select COUNT(id) count from licence_apply where syscodeid = ao.syscodeid and applytype = 2) renewalShopCnt ")
                // 证照过期店铺数量
                .append(" ,(SELECT COUNT(DISTINCT la.id) count FROM licence_apply la,licence_apply_detail lad ")
                .append(" WHERE la.syscodeid = ao.syscodeid AND la.applytype <> 0 and la.id = lad.applyid and lad.applystatue = 3 ")
                .append(" )overdueLicenceCnt ")
                .append(" ,ao.opendate,acp.cost,acp.consumetype,acp.minimum,acp.upperlimit,aab1.balance,aab2.balance creditLimit,mo.disabledflag ")
                .append(" ,case when osi.id is null or osi.imgurl='' then osi1.imgurl else osi.imgurl end imgurl ");
        sql.append(" FROM ")
                .append(" admin_organ ao ")
                .append(" INNER JOIN base_code bc on (bc.id = ao.syscodeid) ")
                .append(" INNER JOIN admin_platform ap ON (ao.id = ap.organid and ap.disabledflag = 0) ")
                .append(" INNER JOIN admin_platform ap1 ON (ao.id = ap1.organid and ap1.isbind = 1 and ap1.disabledflag = 0) ")
                .append(" INNER JOIN admin_consume_product acp on (ao.id = acp.organid) ")
                .append(" INNER JOIN admin_account_balance aab1 on(ao.id = aab1.organid and acp.id = aab1.consumeid and aab1.type= 0) ")
                .append(" INNER JOIN admin_account_balance aab2 on(ao.id = aab2.organid and acp.id = aab2.consumeid and aab2.type = 1) ")
                .append(" INNER JOIN match_organ mo on (mo.syscodeid = ao.syscodeid and mo.type= 1 and tendid = ap1.platform) ")
                .append(" LEFT JOIN organ_shop_img osi on (mo.syscodeid = osi.syscodeid and osi.imgtype = 0 and mo.id = osi.organid) ")
                .append(" LEFT JOIN organ_shop_img osi1 on (mo.syscodeid = osi1.syscodeid and osi1.imgtype = 0 and osi1.organtype=0) ");
        sql.append(" where 1 =1 ");
        if (StringUtils.isNotEmpty(info.getOrgname())) {
            sql.append(" and ao.organname like CONCAT('%',#{orgname},'%') ");
        }
        if (null != info.getPlatform()) {
            sql.append(" and ap.platform = #{platform} ");
        }
        if (null != info.getSysCode()) {
            sql.append(" AND ao.syscode LIKE CONCAT('%',#{sysCode},'%') ");
        }
        if (null != info.getBalancetype()) {
            switch (info.getBalancetype()) {
                case 1:
                    sql.append(" and aab1.balance >= #{balance} ");
                    break;
                case 2:
                    sql.append(" and aab1.balance <= #{balance} ");
                    break;
                case 3:
                    sql.append(" and aab1.balance = #{balance} ");
                    break;
                case 4:
                    sql.append(" and aab1.balance > #{balance} ");
                    break;
                case 5:
                    sql.append(" and aab1.balance < #{balance} ");
                    break;
                case 6:
                    sql.append(" and aab1.balance <> #{balance} ");
                    break;
                default:
                    break;
            }
        }

        if (null != info.getConsumetype()) {
            sql.append(" and acp.consumetype = #{consumetype} ");
        }
        sql.append(" group by ao.syscodeid ");
        sql.append(" ORDER BY waitCheckShopCnt desc ,renewalShopCnt desc,overdueLicenceCnt desc,deniedShopCnt desc ");
        sql.append(" LIMIT #{start},#{length}");
        return sql.toString();
    }

    /**
     * Find count string.
     *
     * @param info the info
     * @return the string
     */
    public String findCount(OrganManagementInfo info) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT  ")
                .append(" count(DISTINCT ao.syscodeid) ");
        sql.append(" FROM ")
                .append(" admin_organ ao ")
                .append(" INNER JOIN base_code bc on (bc.id = ao.syscodeid) ")
                .append(" INNER JOIN admin_platform ap ON (ao.id = ap.organid and ap.disabledflag = 0) ")
                .append(" INNER JOIN admin_consume_product acp on (ao.id = acp.organid) ")
                .append(" INNER JOIN admin_account_balance aab on(ao.id = aab.organid and acp.id = aab.consumeid) ")
                .append(" INNER JOIN match_organ mo ON (mo.syscodeid = ao.syscodeid AND mo.type = 1 AND tendid = ap.platform) ");
        sql.append(" where aab.type = 0 ");
        if (StringUtils.isNotEmpty(info.getOrgname())) {
            sql.append(" and ao.organname like CONCAT('%',#{orgname},'%') ");
        }
        if (null != info.getPlatform()) {
            sql.append(" and ap.platform = #{platform} ");
        }
        if (null != info.getSysCode()) {
            sql.append(" AND ao.syscode LIKE CONCAT('%',#{sysCode},'%') ");
        }
        if (null != info.getBalancetype()) {
            switch (info.getBalancetype()) {
                case 1:
                    sql.append(" and aab.balance >= #{balance} ");
                    break;
                case 2:
                    sql.append(" and aab.balance <= #{balance} ");
                    break;
                case 3:
                    sql.append(" and aab.balance = #{balance} ");
                    break;
                case 4:
                    sql.append(" and aab.balance > #{balance} ");
                    break;
                case 5:
                    sql.append(" and aab.balance < #{balance} ");
                    break;
                case 6:
                    sql.append(" and aab.balance <> #{balance} ");
                    break;
                default:
                    break;
            }
        }
        if (null != info.getConsumetype()) {
            sql.append(" and acp.consumetype = #{consumetype} ");
        }
        return sql.toString();
    }
}
